﻿using Chloe.MySql;
using Kang.DbDoc.Abstracts;
using Kang.DbDoc.DatabaseHelper;
using Kang.DbDoc.Domains;
using Kang.DbDoc.Enums;
using Kang.DbDoc.Interfaces;
using Kang.DbDoc.Models;
using Kang.ExtendMethod;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Kang.DbDoc.ServicesImpl
{
    public class MySqlConnectionService : BaseConnection, IConnectionService
    {
        public string GetConnectionString(DatabaseModel databaseModel)
        {
            var mysqlBuilder = new MySqlConnectionStringBuilder();
            mysqlBuilder.Server = databaseModel.DatabaseHost;
            mysqlBuilder.Port = (uint)databaseModel.DatabasePort;
            mysqlBuilder.Database = databaseModel.DatabaseName;
            mysqlBuilder.UserID = databaseModel.DatabaseUsername;
            mysqlBuilder.Password = databaseModel.DatabasePassword;
            mysqlBuilder.ConnectionTimeout = (uint)databaseModel.TimeOut;
            mysqlBuilder.SslMode = databaseModel.IsSslMode ? MySqlSslMode.Required : MySqlSslMode.None;
            mysqlBuilder.AllowPublicKeyRetrieval = true;
            mysqlBuilder.OldGuids = true;
            return mysqlBuilder.ToString();
        }

        public void SetDefault(DatabaseModel databaseModel)
        {
            databaseModel.DatabasePort = 3306;
            databaseModel.DatabaseUsername = "root";
            if (databaseModel.DatabaseHost.IsBlank())
            {
                databaseModel.DatabaseHost = "127.0.0.1";
            }
        }

        public void LoadDatabaseData(DatabaseModel databaseModel, string SearchWord)
        {
            string sql = @"
                SELECT
                    a.TABLE_NAME as 'table_name',
                    a.TABLE_COMMENT as 'table_comment',
                    a.TABLE_TYPE as 'table_type',
                    b.COLUMN_NAME as 'column_name',
                    b.DATA_TYPE as 'column_type',
                    b.CHARACTER_MAXIMUM_LENGTH as 'column_length',
                    b.NUMERIC_SCALE as 'column_scale',
                    b.COLUMN_KEY as 'column_key_type',
                    b.EXTRA as 'column_extra',
                    b.IS_NULLABLE as 'column_null',
                    b.COLUMN_DEFAULT as 'column_default',
                    b.COLUMN_COMMENT as 'column_comment'
                FROM
                    information_schema.TABLES a,
                    information_schema.COLUMNS b
                WHERE
                    b.TABLE_NAME = a.TABLE_NAME AND a.TABLE_NAME LIKE '%" + SearchWord.StringTrim() + @"%' 
                    AND  b.TABLE_SCHEMA = a.TABLE_SCHEMA
                    AND a.TABLE_SCHEMA = ";
            string sqlViewsInfo = @"
                SELECT
                    a.TABLE_NAME as 'table_name',
                    a.VIEW_DEFINITION as 'view_definition',
                    a.DEFINER as 'definer'
                FROM
                    information_schema.VIEWS a
                WHERE
                    a.TABLE_NAME LIKE '%" + SearchWord.StringTrim() + @"%' 
                    AND a.TABLE_SCHEMA = ";
            string sqlRoutine = @"
                SELECT
                    a.ROUTINE_NAME as 'routine_name',
                    a.ROUTINE_COMMENT as 'routine_comment',
                    a.ROUTINE_TYPE as 'routine_type',
                    a.DATA_TYPE as 'data_type',
                    a.ROUTINE_DEFINITION as 'routine_definition',
                    a.DEFINER as 'definer'
                FROM
                    information_schema.ROUTINES a
                WHERE
                    a.ROUTINE_NAME LIKE '%" + SearchWord.StringTrim() + @"%' 
                    AND a.ROUTINE_SCHEMA = ";
            using (var my = new MySqlContext(new MySqlConnectionFactory(databaseModel.ConnectionString)))
            {
                var tableColumnList = my.SqlQuery<DbTableColumn>($"{sql.StringTrim()} '{my.Session.CurrentConnection.Database}';")
                .ToList();
                var viewList = my.SqlQuery<DbView>($"{sqlViewsInfo.StringTrim()} '{my.Session.CurrentConnection.Database}';")
                .ToList();
                tableColumnList.GroupBy(x => x.TableName).Select(x => new { TableName = x.Key, TableType = x.First().TableType, TableComment = x.First().TableComment, Columns = x }).ToList().ForEach(x =>
                {
                    var item = new TableOrViewModel()
                    {
                        Name = x.TableName,
                        IsCheck = false,
                        Type = GetTableType(x.TableType),
                        Comment = x.TableComment,
                    };
                    foreach (var c in x.Columns)
                    {
                        item.Columns.Add(new ColumnModel()
                        {
                            Name = c.ColumnName,
                            Comment = c.ColumnComment,
                            Type = c.ColumnType,
                            Length = c.ColumnLength,
                            Scale = c.ColumnScale,
                            KeyType = c.ColumnKeyType,
                            Extra = c.ColumnExtra,
                            IsAutoIncrement = c.ColumnExtra?.Contains("auto_increment") == true,
                            IsNull = c.ColumnNull == "YES",
                            Default = c.ColumnDefault
                        });
                    }
                    App.Current.Dispatcher.Invoke(() =>
                    {
                        switch (item.Type)
                        {
                            case TableTypeEnum.Table:
                                databaseModel.Tables.Add(item);
                                break;
                            case TableTypeEnum.View:
                                item.ViewDefiner = viewList.Where(v => v.Name == item.Name).Select(v => v.Definer).FirstOrDefault();
                                item.ViewDefinition = viewList.Where(v => v.Name == item.Name).Select(v => v.ViewDefinition).FirstOrDefault();
                                databaseModel.Views.Add(item);
                                break;
                        }
                    });
                });
                var routineList = my.SqlQuery<DbRoutine>($"{sqlRoutine.StringTrim()} '{my.Session.CurrentConnection.Database}';")
                .ToList();
                routineList.ForEach(x =>
                {
                    var item = new RoutineModel()
                    {
                        Name = x.Name,
                        IsCheck = false,
                        Type = GetRoutineType(x.Type),
                        Comment = x.Comment,
                        DataType = x.DataType,
                        Definition = x.Definition,
                        Definer = x.Definer,
                    };
                    App.Current.Dispatcher.Invoke(() =>
                    {
                        switch (item.Type)
                        {
                            case RoutineTypeEnum.Function:
                                databaseModel.Functions.Add(item);
                                break;
                            case RoutineTypeEnum.Procedure:
                                databaseModel.Procedures.Add(item);
                                break;
                        }
                    });
                });
            }
        }
    }
}
